package com.cyy.dao;

import com.cyy.bean.history;
import com.cyy.bean.history;
import com.cyy.bean.history;
import com.cyy.bean.record;
import com.cyy.util.dbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class historyDao extends recordDao {

    public ArrayList<record> selectAll(String name,int type1) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql;
        if(type1==0) {
            sql = "select history.recordId,productName,price,record.time,num,usability,history.state from history inner join record on history.recordId=record.recordId where record.userName=?";
        }else {
            sql = "select history.recordId,productName,price,record.time,num,usability,history.state from history inner join record on history.recordId=record.recordId where record.userName=? and usability=1";
        }
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, name);
        ResultSet resultSet = pstmt.executeQuery();
        history data = null;
        int price, num, id,usability,state;
        String productName;
        ArrayList<record> datas = null;
        String dateTime;
        while (resultSet.next()) {
            productName = resultSet.getString("productName");
            price = resultSet.getInt("price");
            num = resultSet.getInt("num");
            id = resultSet.getInt("recordId");
            usability = resultSet.getInt("usability");
            dateTime=resultSet.getString("time");
            state=resultSet.getInt("state");
            data = new history(id, name, productName, price, num, dateTime, state,usability);
            if (datas == null) {
                datas = new ArrayList<>();
            }
            datas.add(data);
        }
        dbUtil.close(resultSet, pstmt, conn);
        return datas;
    }

    public ArrayList<history> selectAll() throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        int recordId, state, price, num,usability;
        String userName, productName, time;
        history data = null;
        ArrayList<history> datas = null;
//        通过内连接查询两个表，目的是得到所以用户的与购买相关的历史记录
        String sql = "select * from history inner join record using(recordId)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            recordId = resultSet.getInt("recordId");
            state = resultSet.getInt("state");
            userName = resultSet.getString("userName");
            productName = resultSet.getString("productName");
            price = resultSet.getInt("price");
            num = resultSet.getInt("num");
            time = resultSet.getString("time");
            usability = resultSet.getInt("usability");
            data = new history(recordId, userName, productName, price, num, time,state,usability);
            if (datas == null) {
                datas = new ArrayList<>();
            }
            datas.add(data);
        }
        return datas;
    }

    public void add(record h) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="insert into record(userName,productName,price,num,time,usability) values(?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,h.getUserName());
        pstmt.setString(2,h.getProductName());
        pstmt.setInt(3,h.getPrice());
        pstmt.setInt(4,h.getNum());
        pstmt.setString(5,h.getTime());
        pstmt.setInt(6,h.getUsability());
        int i = pstmt.executeUpdate();
        int id=getMaxid();
        String sql1="insert into history values(?,?)";
        PreparedStatement pstmt1 = conn.prepareStatement(sql1);
        pstmt1.setInt(1,id);
        pstmt1.setInt(2,((history)h).getState());
        int i1 = pstmt1.executeUpdate();
        dbUtil.close(null,pstmt,conn);
        dbUtil.close(null,pstmt1,conn);
    }

    public int selectByProductAndUser(String userName, String productName) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select * from history where recordId in (select recordId from record where userName=? and productName=?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        pstmt.setString(2,productName);
        ResultSet resultSet = pstmt.executeQuery();
        if(resultSet.next())
        {
            return resultSet.getInt("recordId");
        }
        dbUtil.close(null,pstmt,conn);
        return 0;
    }

    public void deleteByUserAndProduct(String userName, String productName,String time1) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select recordId from history where recordId in (select recordId from record where userName=? and productName=? and record.time=?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, userName);
        pstmt.setString(2, productName);
        pstmt.setString(3,time1);
        ResultSet resultSet= pstmt.executeQuery();
        String sql1="",sql2="";
        PreparedStatement pstmt1=null,pstmt2=null;
        while(resultSet.next())
        {
            int recordId = resultSet.getInt("recordId");
//            删除history中的记录
            sql1="delete from history where recordId=?";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setInt(1,recordId);
            pstmt1.executeUpdate();
//            删除总记录
            sql2="delete from record where recordId=?";
            pstmt2 = conn.prepareStatement(sql2);
            pstmt2.setInt(1,recordId);
            pstmt2.executeUpdate();
        }
        dbUtil.close(null,pstmt,conn);
        dbUtil.close(null,pstmt1,conn);
        dbUtil.close(null,pstmt2,conn);
    }

    public int getNumByname(String userName) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select * from history inner join record on history.recordId=record.recordId having state=0 and userName=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        ResultSet resultSet = pstmt.executeQuery();
        int index=0;
        ArrayList<String> times=new ArrayList<String>();
        times.add("");
        String time="";
        while(resultSet.next())
        {
            time=resultSet.getString("time");
            if(!times.contains(time))
            {
                index+=1;
                times.add(time);
            }
        }
        dbUtil.close(resultSet,pstmt,conn);
        return index;
    }
    public static ArrayList<record> selectNoConfirm(String userName) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="select * from history inner join record on history.recordId=record.recordId having state=0 and userName=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        ResultSet resultSet = pstmt.executeQuery();
        int num,price;
        String productName,time;
        ArrayList<record> records=null;
        while(resultSet.next())
        {
            productName=resultSet.getString("productName");
            price=resultSet.getInt("price");
            num=resultSet.getInt("num");
            time=resultSet.getString("time");
            if(records==null)
            {
                records=new ArrayList<record>();
            }
            records.add(new record(0,"",productName,price,num,time,1));
        }
        dbUtil.close(resultSet,pstmt,conn);
        return records;
    }
    public void updateState(String userName,String time) throws SQLException, ClassNotFoundException {
        Connection conn = dbUtil.getConnection();
        String sql="update history set state =1 where recordId in (select recordId from record where userName=? and time=?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,userName);
        pstmt.setString(2,time);
        int i = pstmt.executeUpdate();
        dbUtil.close(null,pstmt,conn);
    }
}
